
# Importing Libraries

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import matplotlib.ticker as ticker
import matplotlib.dates as mdates


# mounting the drive to google colab
from google.colab import drive
drive.mount('/content/drive')

Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount("/content/drive", force_remount=True).


# Path to the data in google drive
file_path1 = '/content/drive/My Drive/Project_Capstone_2/FACT_MARKET_DEMAND.csv'
file_path2 = '/content/drive/My Drive/Project_Capstone_2/consumer_demographics.csv'
file_path3 = '/content/drive/My Drive/Project_Capstone_2/zip_to_market_unit_mapping.csv'


# Load data into a Pandas DataFrames
market_demand = pd.read_csv(file_path1)
consumer_demographics = pd.read_csv(file_path2)
zip_market = pd.read_csv(file_path3)


# Exploring the first five rows of data
market_demand.head()


# Understanding data types of each column in the fact market demand dataset
market_demand.dtypes

DATE                object
MARKET_KEY           int64
CALORIC_SEGMENT     object
CATEGORY            object
UNIT_SALES         float64
DOLLAR_SALES       float64
MANUFACTURER        object
BRAND               object
PACKAGE             object
ITEM                object
dtype: object


# Getting the count of total rows in market_demand dataset
total_rows = len(market_demand)
print(f"Total count of rows in the DataFrame: {total_rows}")

Total count of rows in the DataFrame: 24461424


# Checking for null values in the dataset
print(market_demand.isnull().sum())

DATE                   0
MARKET_KEY             0
CALORIC_SEGMENT    59725
CATEGORY               0
UNIT_SALES             0
DOLLAR_SALES           0
MANUFACTURER           0
BRAND                  0
PACKAGE                0
ITEM                   0
dtype: int64


consumer_demographics.head()


# Understanding data types of each column in the consumer demographics dataset
consumer_demographics.dtypes

Geography Name    object
Zip                int64
City              object
State             object
Segment           object
Criteria          object
Count              int64
Criteria_Unit     object
dtype: object


# Getting the count of total rows in the consumer_demographics dataset
total_rows = len(consumer_demographics)
print(f"Total count of rows in the DataFrame: {total_rows}")

Total count of rows in the DataFrame: 491400


# Checking for any null values in the consumer_demographics dataset
print(consumer_demographics.isnull().sum())

Geography Name    0
Zip               0
City              0
State             0
Segment           0
Criteria          0
Count             0
Criteria_Unit     0
dtype: int64


# Understanding the zip_market dataset
zip_market.head()


# Getting the count of total rows in the zip_market dataset
total_rows = len(zip_market)
print(f"Total count of rows in the DataFrame: {total_rows}")

Total count of rows in the DataFrame: 2382


# Checking for any null values in the zip_market dataset
print(zip_market.isnull().sum())

ZIP_CODE      0
MARKET_KEY    0
dtype: int64


top_10_products = market_demand.groupby('ITEM')['UNIT_SALES'].sum().nlargest(10).index  # Grouping by ITEM and UNIT_SALES and selecting top 10 of sum.


# Creating a copy if lost
top_10_data = market_demand[market_demand['ITEM'].isin(top_10_products)].copy()

# Converting 'DATE' to datetime and sorting the DataFrame
top_10_data['DATE'] = pd.to_datetime(top_10_data['DATE'])
top_10_data.sort_values('DATE', inplace=True)

# Grouping by 'ITEM' and resampling by year, then summing up the unit sales
annual_data = top_10_data.groupby('ITEM').resample('Y', on='DATE').sum(numeric_only=True)

# Resetting the index to make 'ITEM' and 'DATE' regular columns again
annual_data.reset_index(inplace=True)

# Plotting the annual data
plt.figure(figsize=(15, 12))
sns.lineplot(data=annual_data, x=annual_data['DATE'].dt.year, y='UNIT_SALES', hue='ITEM')

# Setting the x-axis labels to be full years
plt.xticks(ticks=[2020, 2021, 2022, 2023], labels=['2020', '2021', '2022', '2023'])

plt.title('Annual Trend in Demand for Top 10 Products')
plt.xlabel('Year')
plt.ylabel('Unit Sales')
plt.legend(title='Product', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.tight_layout()
plt.show()


# Plotting correlation matrix between the 'UNIT_SALES and 'DOLLAR_SALES'
numeric_vars = ['UNIT_SALES', 'DOLLAR_SALES']
correlation_matrix = market_demand[numeric_vars].corr()
plt.figure(figsize=(8, 6))
sns.heatmap(correlation_matrix, annot=True, cmap='coolwarm', fmt='.2f')
plt.title('Heatmap of Correlation Matrix (Swire-CC)')
plt.show()


# Converting the 'DATE' column to datetime format
market_demand['DATE'] = pd.to_datetime(market_demand['DATE'])

# Setting the DATE as the index
market_demand.set_index('DATE', inplace=True)

# Grouping by 'ITEM' and resampling to weekly, summing up the 'UNIT_SALES' and 'DOLLAR_SALES'
weekly_sales = market_demand.groupby('ITEM')['UNIT_SALES'].resample('W').sum().reset_index()
weekly_sales_dollar = market_demand.groupby('ITEM')['DOLLAR_SALES'].resample('W').sum().reset_index()

# Sorting to ensure the order is correct for diff calculation
weekly_sales.sort_values(by=['ITEM', 'DATE'], inplace=True)

# Calculating week-over-week percentage change in 'UNIT_SALES' and 'DOLLAR_SALES'
weekly_sales['WOW_CHANGE'] = weekly_sales.groupby('ITEM')['UNIT_SALES'].pct_change() * 100
weekly_sales_dollar['WOW_CHANGE'] = weekly_sales_dollar.groupby('ITEM')['DOLLAR_SALES'].pct_change() * 100

# Finding the top 10 items by total 'UNIT_SALES' over the entire period
top_items = weekly_sales.groupby('ITEM')['UNIT_SALES'].sum().nlargest(10).index
top_items_dollar = weekly_sales_dollar.groupby('ITEM')['DOLLAR_SALES'].sum().nlargest(10).index

# Filtering the weekly_sales DataFrame to only include these top items
top_items_weekly_sales = weekly_sales[weekly_sales['ITEM'].isin(top_items)]


# Plotting time-series
market_demand.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6), color='purple') # plotting overall market demand over time
market_demand.groupby('DATE')['DOLLAR_SALES'].sum().plot(figsize=(12, 6), color='blue') # plotting overall market demand over time

plt.title('Overall Market Demand Over Time')
plt.xlabel('Date')
plt.ylabel('Sales')
plt.legend()
plt.show()


# Extracting the first few rows of the selected_rows starting from 1 as in 0 there is no prior week data
selected_rows = top_items_weekly_sales.iloc[1:10]
selected_rows


# Getting the distinct or unique in manufacturer column

distinct_manufacturers = market_demand['MANUFACTURER'].unique()

# Printing the distinct manufacturers
print(distinct_manufacturers)

['SWIRE-CC' 'COCOS' 'JOLLYS' 'JORDYS' 'BEARS' 'PONYS' 'ALLYS' 'KEKES']


# Plotting the manufacture vs sales (both unit and dollar)
market_demand.reset_index(inplace=True)
plt.figure(figsize=(12, 6))  # Creating the plot size.
plt.subplot(1, 2, 1) # Subplot for unit sales.
unique_manufacturers = market_demand['MANUFACTURER'].unique() # Finding the unique manufactures in the manufactures column.

# Plotting the unit sales for each unique manufactures.
for manufacturer in unique_manufacturers:
    subset = market_demand[market_demand['MANUFACTURER'] == manufacturer]
    plt.bar(manufacturer, subset['UNIT_SALES'].sum(), label=manufacturer)

# Assigning the title, xlabel and ylabel along with legend.

plt.title('Manufacturer vs Unit Sales')
plt.xlabel('Manufacturer')
plt.ylabel('Unit Sales')
plt.legend()
plt.xticks(rotation=45)

plt.subplot(1, 2, 2) # Subplot for dollar sales.
unique_manufacturers = market_demand['MANUFACTURER'].unique()

# Plotting the dollar sales for each unique manufactures.
for manufacturer in unique_manufacturers:
    subset = market_demand[market_demand['MANUFACTURER'] == manufacturer]
    plt.bar(manufacturer, subset['DOLLAR_SALES'].sum(), label=manufacturer)

# Assigning the title, xlabel and ylabel along with legend.
plt.title('Manufacturer vs Dollar Sales')
plt.xlabel('Manufacturer')
plt.ylabel('Dollar Sales')
plt.legend()
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()


# Filtering the rows based on the different manufactures.
swire = market_demand[market_demand['MANUFACTURER'] == 'SWIRE-CC']
cocos = market_demand[market_demand['MANUFACTURER'] == 'COCOS']
jolly = market_demand[market_demand['MANUFACTURER'] == 'JOLLYS']
jordys = market_demand[market_demand['MANUFACTURER'] == 'JORDYS']
bears = market_demand[market_demand['MANUFACTURER'] == 'BEARS']
ponys = market_demand[market_demand['MANUFACTURER'] == 'PONYS']
allys = market_demand[market_demand['MANUFACTURER'] == 'ALLYS']
keks = market_demand[market_demand['MANUFACTURER'] == 'KEKES']


# Plotting the unit sales for different manufactures over the time period.
swire.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
cocos.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
jolly.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
jordys.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
bears.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
ponys.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
allys.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
keks.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
plt.title('Overall Market Demand Over Time')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['SWIRE-CC','COCOS','JOLLYS','JORDYS','BEARS','PONYS','ALLYS','KEKES'],bbox_to_anchor=(1.05, 1), loc='upper left')
plt.grid(True, which='both', linestyle='--', linewidth=0.5)
plt.show()


# Plotting the SWIRE-CC dataset to plot the unit sales over time period
swire.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='red')
plt.title('Overall Market Demand Over Time')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['SWIRE-CC'])
plt.grid(True, which='both', linestyle='--', linewidth=0.5)
plt.show()


# Creating the new dataset as diet by filtering the swire dataset where the caloric segment is only Diet/Light
diet= swire[swire['CALORIC_SEGMENT'] == 'DIET/LIGHT']


# Creating the new dataset as regular by filtering the swire dataset where the caloric segment is only Regular
regular= swire[swire['CALORIC_SEGMENT'] == 'REGULAR']


# Plotting the overall market demand over time for diet/light and regular caloric segment in SWIRE-CC
diet.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='blue')
regular.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='red')
plt.title('Overall Market Demand Over Time of diet/light and regular in Swire-cc')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['DIET','REGULAR'])
plt.grid(True, which='both', linestyle='--', linewidth=0.5)
plt.show()


# Creating the new dataset energy by filtering the diet (in SWIRE-CC) dataset where the category is energy.
energy = diet[diet['CATEGORY'] == 'ENERGY']


# Creating the new dataset swater by filtering the diet (in SWIRE-CC) dataset where the category is sparkling water.
swater= diet[diet['CATEGORY'] == 'SPARKLING WATER']


# Plotting the overall market demand of energy and sparkling categories over time in diet segment over swire-cc.
energy.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='violet')
swater.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='black')
plt.title('Overall Market Demand Over Time in DIET category')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['ENERGY','SPARKLING WATER'])
plt.grid(True, which='both', linestyle='--', linewidth=0.5)
plt.show()


# Creating the new datasets SSD and Enhanced by filtering the regular (in SWIRE-CC) dataset where the category is SSD and ING Enhanced Water respectively.
ssd= regular[regular['CATEGORY'] == 'SSD']
enhanced= regular[regular['CATEGORY'] == 'ING ENHANCED WATER']


# Plotting the overall market demand over time in regular category
ssd.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='red')
enhanced.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='blue')
plt.title('Overall Market Demand Over Time in REGULAR category')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['SSD', 'ING ENHANCED WATER'])
plt.grid(True, which='both', linestyle='--', linewidth=0.5)
plt.show()


# Checking for unique values in the Category column of Market Demand dataset
distinct_categories = market_demand['CATEGORY'].unique()
print(distinct_categories)

['SSD' 'ING ENHANCED WATER' 'SPARKLING WATER' 'ENERGY' 'COFFEE']


market_demand['DATE'] = pd.to_datetime(market_demand['DATE'])
grouped = market_demand.groupby(['DATE', 'CATEGORY']).agg({'UNIT_SALES':'sum', 'DOLLAR_SALES':'sum'}).reset_index()

# Pivoting the data for easier plotting
pivot_unit_sales = grouped.pivot(index='DATE', columns='CATEGORY', values='UNIT_SALES')
pivot_dollar_sales = grouped.pivot(index='DATE', columns='CATEGORY', values='DOLLAR_SALES')

# Plotting time-series visualizations
plt.figure(figsize=(14, 7))

# Unit Sales
plt.subplot(1, 2, 1)
plt.plot(pivot_unit_sales.index, pivot_unit_sales, marker='', linewidth=2)
plt.title('Unit Sales by Category Over Time')
plt.legend(pivot_unit_sales.columns,loc='center right')
plt.xticks(rotation=45)

# Dollar Sales
plt.subplot(1, 2, 2)
plt.plot(pivot_dollar_sales.index, pivot_dollar_sales, marker='', linewidth=2)
plt.title('Dollar Sales by Category Over Time')
plt.legend(pivot_dollar_sales.columns)
plt.xticks(rotation=45)

plt.tight_layout()
plt.show()


# Filtering the rows based on different categories in diet/light caloric segment
market_demand_swire=market_demand[market_demand['MANUFACTURER'] == 'SWIRE-CC']
total_ssd=market_demand_swire[market_demand_swire['CATEGORY'] == 'SSD']
total_energy=market_demand_swire[market_demand_swire['CATEGORY'] == 'ENERGY']
total_sparkling= market_demand_swire[market_demand_swire['CATEGORY'] == 'SPARKLING WATER']
total_ingwater= market_demand_swire[market_demand_swire['CATEGORY'] == 'ING ENHANCED WATER']


# Filtering the rows based on different brands in different categories of diet/light caloric segment.
dietsmash=total_ssd[total_ssd['BRAND']=='DIET SMASH']
peppy=total_ssd[total_ssd['BRAND']=='PEPPY']
sparkling= total_ssd[total_ssd['BRAND']=='SPARKLING JACCEPTABLETLESTER']
blast=total_energy[total_energy['BRAND']=='VENOMOUS BLAST']
moonlight=total_energy[total_energy['BRAND']=='DIET MOONLIT']
square= total_sparkling[total_sparkling['BRAND']=='SQUARE']
greetingle=total_ingwater[total_ingwater['BRAND']=='GREETINGLE']


# Plotting overall market demand for brands such as diet smash,peppy,sparkling water.
dietsmash.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6), color='blue')
peppy.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='red')
sparkling.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='pink')
plt.title('Overall Market Demand Over Time in diet category')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['Diet Smash', 'Peppy', 'Sparkling JACCEPTABLETLESTER'])
plt.show()


# Plotting overall market demand for Blast and Moonlight brands in energy category.
blast.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='purple')
moonlight.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='violet')
plt.title('Overall Market Demand Over Time in energy category')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['Blast', 'Moonlit'])
plt.show()


# Plotting overall market demand for Square and Greentingale brands in diet/light category.
square.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='blue')
greetingle.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='green')
plt.title('Overall Market Demand Over Time in category')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['Square', 'Greentingale'])
#plt.legend(title='Category', bbox_to_anchor=(1, 1))
plt.show()


# Filtering the data based on different brands in the ssd category
dietssdsmash= dietsmash[dietsmash['CALORIC_SEGMENT']=='DIET']
regularssdpeppy=peppy[peppy['CALORIC_SEGMENT']=='REGULAR']
regularssdsparkling=sparkling[sparkling['CALORIC_SEGMENT']=='REGULAR']
# Filtering the data based on different brands in the ssd category, Both gives same result
# ssddietsmash=ssd[ssd['BRAND']=='DIET SMASH']
# ssdpeppy=ssd[ssd['BRAND']=='PEPPY']
# ssdsparkling= ssd[ssd['BRAND']=='SPARKLING JACCEPTABLETLESTER']


# Plotting overall market demand over time for different brands in ssd category.
dietssdsmash.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='yellow')
regularssdpeppy.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='red')
regularssdsparkling.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6),color='blue')
plt.title('Overall Market Demand Over Time in category')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['SSD Diet Smash', 'SSD Peppy', 'SSD Sparkling JACCEPTABLETLESTER'])
plt.show()


# Filtering the data based on different brands in the energy category
energyblast=energy[energy['BRAND']=='VENOMOUS BLAST']
energymoonlight=energy[energy['BRAND']=='DIET MOONLIT']
# dietblast=blast[blast['CALORIC_SEGMENT']=='DIET']
# dietmoonlight=moonlight[moonlight['CALORIC_SEGMENT']=='DIET']


# Plotting overall market demand over time for different brands in energy category.
energyblast.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6), color='violet')
energymoonlight.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6), color='blue')

plt.title('Overall Market Demand Over Time in diet category')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['Energy Blast', 'Energy Moonlit'])
plt.show()


# Filtering the data based on brands in the category of sparkling water and ing-enhanced water.
sparklingsquare= swater[swater['BRAND']=='SQUARE']
enhancedgreetingle= enhanced[enhanced['BRAND']=='GREETINGLE']


# Plotting overall market demand over time for different brands in sparkling water and ing-enhanced category.
sparklingsquare.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
enhancedgreetingle.groupby('DATE')['UNIT_SALES'].sum().plot(figsize=(12, 6))
plt.title('Overall Market Demand Over Time in diet category')
plt.xlabel('Date')
plt.ylabel('Unit Sales')
plt.legend(['Sparkling Square', 'Enhanced Greetingale'])
plt.show()


distinct_caloric = market_demand['CALORIC_SEGMENT'].unique()

# Print the distinct manufacturers
print(distinct_caloric)

['DIET/LIGHT' 'REGULAR' nan]


#replace nan to none for CALORIC_SEGMENT column

market_demand['CALORIC_SEGMENT'] = market_demand['CALORIC_SEGMENT'].fillna('None')
distinct_caloric = market_demand['CALORIC_SEGMENT'].unique()

# Print the distinct manufacturers
print(distinct_caloric)

['DIET/LIGHT' 'REGULAR' 'None']


# total dollar sales by caloric segment
print(market_demand.groupby(['CALORIC_SEGMENT'])['DOLLAR_SALES'].sum())

CALORIC_SEGMENT
DIET/LIGHT    5.378058e+09
None          8.100379e+06
REGULAR       9.073970e+09
Name: DOLLAR_SALES, dtype: float64


# Perform groupby operation
grouped_data = market_demand.groupby(['CALORIC_SEGMENT'])['DOLLAR_SALES'].sum()
# Extracting data for plotting
caloric_segments = grouped_data.index
dollar_sales = grouped_data.values

# Plotting
plt.figure(figsize=(10, 6))  # Adjust the figure size as per your preference
plt.bar(caloric_segments, dollar_sales, color=['orange','red','blue'])

# Adding labels and title
plt.xlabel('Caloric Segment')
plt.ylabel('Total Dollar Sales')
plt.title('Total Dollar Sales by Caloric Segment')

# Rotating x-axis labels for better readability
plt.xticks(rotation=45, ha='right')

# Display the plot
plt.tight_layout()  # Adjust layout to prevent labels from getting cut off
plt.show()


# removing the None rows in the caloric segment.
market_demand=market_demand[market_demand['CALORIC_SEGMENT'] != 'None']


# plot using matplotlib to have multiple bar plot to get dollar sales of CALORIC_SEGMENT for year to year

market_demand['YEAR'] = pd.to_datetime(market_demand['DATE'])
market_demand['YEAR']=market_demand['YEAR'].dt.year
# Group data by year and caloric segment
grouped_data = market_demand.groupby(['YEAR', 'CALORIC_SEGMENT'])['DOLLAR_SALES'].sum().reset_index()

# Create a dictionary to store data for each year
year_data = {}
for year in grouped_data['YEAR'].unique():
    year_data[year] = grouped_data[grouped_data['YEAR'] == year]

distinct_caloric = grouped_data['CALORIC_SEGMENT'].unique()

# Create a figure and axes
fig, ax = plt.subplots(figsize=(15, 6))

# Set title and labels
plt.title('Dollar Sales of Caloric Segment per Year')
plt.xlabel('Year')
plt.ylabel('Dollar Sales')

# Set x-axis ticks and labels
x_ticks = np.arange(len(year_data))
x_labels = list(year_data.keys())
plt.xticks(x_ticks, x_labels)

# Set bar width
bar_width = 0.8 / len(distinct_caloric)

for i, caloric_segment in enumerate(distinct_caloric):
  caloric_data = []
  for year, data in year_data.items():
    caloric_data.append(data[data['CALORIC_SEGMENT'] == caloric_segment]['DOLLAR_SALES'].values[0])
  ax.bar(x_ticks + i * bar_width, caloric_data, width=bar_width, label=caloric_segment)


# Add legend and show plot
plt.legend(title='Caloric Segment')
plt.show()


# plot using matplotlib to have multiple bar plot to get dollar sales of CALORIC_SEGMENT for year to year

market_demand['YEAR'] = pd.to_datetime(market_demand['DATE'])
market_demand['YEAR']=market_demand['YEAR'].dt.year
# Group data by year and caloric segment
grouped_data = market_demand.groupby(['YEAR', 'CALORIC_SEGMENT'])['UNIT_SALES'].sum().reset_index()

# Create a dictionary to store data for each year
year_data = {}
for year in grouped_data['YEAR'].unique():
    year_data[year] = grouped_data[grouped_data['YEAR'] == year]

distinct_caloric = grouped_data['CALORIC_SEGMENT'].unique()

# Create a figure and axes
fig, ax = plt.subplots(figsize=(15, 6))

# Set title and labels
plt.title('Unit Sales of Caloric Segment per Year')
plt.xlabel('Year')
plt.ylabel('Unit Sales')

# Set x-axis ticks and labels
x_ticks = np.arange(len(year_data))
x_labels = list(year_data.keys())
plt.xticks(x_ticks, x_labels)

# Set bar width
bar_width = 0.8 / len(distinct_caloric)

for i, caloric_segment in enumerate(distinct_caloric):
  caloric_data = []
  for year, data in year_data.items():
    caloric_data.append(data[data['CALORIC_SEGMENT'] == caloric_segment]['UNIT_SALES'].values[0])
  ax.bar(x_ticks + i * bar_width, caloric_data, width=bar_width, label=caloric_segment)


# Add legend and show plot
plt.legend(title='Caloric Segment')
plt.show()


# Group by 'PACKET_SIZE' and 'DATE', then calculate sum of 'UNIT_SALES' and 'DOLLAR_SALES'
#.5L Multi Jug
# 10Small MLT
# .5L 12One Jug
# 2L Multi Jug
# 11Small 4One
# 11Small MLT
# 16 Liquid Small
market_demandpacket = market_demand[market_demand['PACKAGE'].isin(['.5L MULTI JUG',
                                                            '10Small MLT',
                                                            '.5L 12ONE JUG',
                                                            '2L MULTI JUG',
                                                            '11SMALL 4ONE',
                                                            '11SMALL MLT',
                                                            '16 LIQUID SMALL'])]
grouped_data = market_demandpacket.groupby(['PACKAGE', 'DATE']).agg({'UNIT_SALES':'sum', 'DOLLAR_SALES':'sum'}).reset_index()

grouped_data.head(10)


# Assuming grouped_data is already defined and 'DATE' is converted to datetime
grouped_data['DATE'] = pd.to_datetime(grouped_data['DATE'])

# Get unique package types and divide them into chunks of 5
package_types = grouped_data['PACKAGE'].unique()
chunks = [package_types[x:x+5] for x in range(0, len(package_types), 5)]

# Iterate over each chunk and create a separate plot for each
for i, chunk in enumerate(chunks, start=1):
    plt.figure(figsize=(10, 6))  # Adjust figure size as needed
    for package in chunk:
        subset = grouped_data[grouped_data['PACKAGE'] == package]
        plt.plot(subset['DATE'], subset['UNIT_SALES'], label=package)

    # Set title and labels with chunk number
    plt.title(f'Time Series of Unit Sales by Package Type - Set {i}')
    plt.xlabel('Date')
    plt.ylabel('Unit Sales')

    # Date formatting
    plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%Y'))
    plt.gca().xaxis.set_major_locator(mdates.YearLocator())

    plt.gcf().autofmt_xdate()  # Improve date readability

    plt.legend()
    plt.tight_layout()
    plt.show()


# Installing required libraries
!pip install wordcloud matplotlib numpy pandas

Requirement already satisfied: wordcloud in /usr/local/lib/python3.10/dist-packages (1.9.3)
Requirement already satisfied: matplotlib in /usr/local/lib/python3.10/dist-packages (3.7.1)
Requirement already satisfied: numpy in /usr/local/lib/python3.10/dist-packages (1.25.2)
Requirement already satisfied: pandas in /usr/local/lib/python3.10/dist-packages (1.5.3)
Requirement already satisfied: pillow in /usr/local/lib/python3.10/dist-packages (from wordcloud) (9.4.0)
Requirement already satisfied: contourpy>=1.0.1 in /usr/local/lib/python3.10/dist-packages (from matplotlib) (1.2.0)
Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.10/dist-packages (from matplotlib) (0.12.1)
Requirement already satisfied: fonttools>=4.22.0 in /usr/local/lib/python3.10/dist-packages (from matplotlib) (4.49.0)
Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.10/dist-packages (from matplotlib) (1.4.5)
Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.10/dist-packages (from matplotlib) (23.2)
Requirement already satisfied: pyparsing>=2.3.1 in /usr/local/lib/python3.10/dist-packages (from matplotlib) (3.1.1)
Requirement already satisfied: python-dateutil>=2.7 in /usr/local/lib/python3.10/dist-packages (from matplotlib) (2.8.2)
Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.10/dist-packages (from pandas) (2023.4)
Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.10/dist-packages (from python-dateutil>=2.7->matplotlib) (1.16.0)


from wordcloud import WordCloud
from PIL import Image

mask_image = np.array(Image.open('/content/ResizedImage936680-coca-cola-bottle-prototype.png'))

# Filtering for 'avocado' flavored items from SWIRE-CC
df_avocado = market_demand[(market_demand['MANUFACTURER'] == 'SWIRE-CC') &
                            (market_demand['ITEM'].str.contains('avocado', case=False, na=False))]

# Generating word cloud for 'avocado'
text = ' '.join(df_avocado['ITEM'].astype(str))
wordcloud_avocado = WordCloud(width=800, height=400, background_color='white', mask=mask_image, contour_width=1, contour_color='black').generate(text)

plt.figure(figsize=(10, 5))
plt.imshow(wordcloud_avocado, interpolation='bilinear')
plt.axis('off')
plt.title('Word Cloud for Avocado Flavored Items - SWIRE-CC Manufacturer')
plt.show()


#mask_image = np.array(Image.open('/content/ResizedImage936680-coca-cola-bottle-prototype.png'))

# Filtering for 'plum' flavored items from SWIRE-CC
df_plum = market_demand[(market_demand['MANUFACTURER'] == 'SWIRE-CC') &
                         (market_demand['ITEM'].str.contains('plum', case=False, na=False))]

# Generating word cloud for 'plum'
text = ' '.join(df_plum['ITEM'].astype(str))
wordcloud_plum = WordCloud(width=800, height=400, background_color='white', mask=mask_image, contour_width=1, contour_color='black').generate(text)

plt.figure(figsize=(10, 5))
plt.imshow(wordcloud_plum, interpolation='bilinear')
plt.axis('off')
plt.title('Word Cloud for Plum Flavored Items - SWIRE-CC Manufacturer')
plt.show()


#mask_image = np.array(Image.open('/content/ResizedImage936680-coca-cola-bottle-prototype.png'))

# Filtering for 'mulberries' flavored items from SWIRE-CC
df_mulberries = market_demand[(market_demand['MANUFACTURER'] == 'SWIRE-CC') &
                               (market_demand['ITEM'].str.contains('mulberries', case=False, na=False))]

# Generating word cloud for 'mulberries'
text = ' '.join(df_mulberries['ITEM'].astype(str))
wordcloud_mulberries = WordCloud(width=800, height=400, background_color='white', mask=mask_image, contour_width=1, contour_color='black').generate(text)

plt.figure(figsize=(10, 5))
plt.imshow(wordcloud_mulberries, interpolation='bilinear')
plt.axis('off')
plt.title('Word Cloud for Mulberries Flavored Items - SWIRE-CC Manufacturer')
plt.show()


#mask_image = np.array(Image.open('/content/ResizedImage936680-coca-cola-bottle-prototype.png'))

# Filtering for 'kiwano' flavored items from SWIRE-CC
df_kiwano = market_demand[(market_demand['MANUFACTURER'] == 'SWIRE-CC') &
                           (market_demand['ITEM'].str.contains('kiwano', case=False, na=False))]

# Generate word cloud for 'kiwano'
text = ' '.join(df_kiwano['ITEM'].astype(str))
wordcloud_kiwano = WordCloud(width=800, height=400, background_color='white', mask=mask_image, contour_width=1, contour_color='black').generate(text)

plt.figure(figsize=(10, 5))
plt.imshow(wordcloud_kiwano, interpolation='bilinear')
plt.axis('off')
plt.title('Word Cloud for Kiwano Flavored Items - SWIRE-CC Manufacturer')
plt.show()


import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import matplotlib.ticker as ticker
import pandas as pd

# Ensuring 'DATE' column in 'df_mulberries' and 'df_plum' is in datetime format
df_mulberries['DATE'] = pd.to_datetime(df_mulberries['DATE'])
df_plum['DATE'] = pd.to_datetime(df_plum['DATE'])

# Setting 'DATE' as the index for both DataFrames
df_mulberries.set_index('DATE', inplace=True)
df_plum.set_index('DATE', inplace=True)

# Resampling and summing 'DOLLAR_SALES' to get total monthly sales in dollars
monthly_sales_dollars_mulberries = df_mulberries['DOLLAR_SALES'].resample('M').sum()
monthly_sales_dollars_plum = df_plum['DOLLAR_SALES'].resample('M').sum()

# Calculating Month-over-Month difference in dollars
mom_difference_dollars_mulberries = monthly_sales_dollars_mulberries.diff()
mom_difference_dollars_plum = monthly_sales_dollars_plum.diff()

# Plotting the Month-over-Month dollar difference
plt.figure(figsize=(14, 7))
plt.plot(mom_difference_dollars_mulberries.index, mom_difference_dollars_mulberries.values, marker='o', linestyle='-', label='Mulberries')
plt.plot(mom_difference_dollars_plum.index, mom_difference_dollars_plum.values, marker='^', linestyle='-', label='Plum')

# Setting title, labels, and legend
plt.title('Month-over-Month Dollar Difference in Sales for Mulberries and Plum Flavored Items - SWIRE-CC')
plt.xlabel('Month')
plt.ylabel('Dollar Difference ($)')

# Formatting the x-axis to show month names and year
plt.gca().xaxis.set_major_locator(mdates.MonthLocator())
plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))

# Rotating date labels for better readability
plt.xticks(rotation=45)

# Formatting y-axis tick labels to show dollar values
plt.gca().yaxis.set_major_formatter(ticker.StrMethodFormatter('${x:,.0f}'))

# Addding grid and legend
plt.grid(True, which='both', linestyle='--', linewidth=0.5)
plt.legend()

plt.tight_layout()
plt.show()

<ipython-input-19-4113155a501b>:7: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df_mulberries['DATE'] = pd.to_datetime(df_mulberries['DATE'])
<ipython-input-19-4113155a501b>:8: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df_plum['DATE'] = pd.to_datetime(df_plum['DATE'])


# Ensuring 'DATE' column in 'df_mulberries' and 'df_plum' is in datetime format
df_avocado['DATE'] = pd.to_datetime(df_avocado['DATE'])
df_kiwano['DATE'] = pd.to_datetime(df_kiwano['DATE'])

# Setting 'DATE' as the index for both DataFrames
df_avocado.set_index('DATE', inplace=True)
df_kiwano.set_index('DATE', inplace=True)

# Resampling and summing 'DOLLAR_SALES' to get total monthly sales in dollars
monthly_sales_dollars_avocado = df_avocado['DOLLAR_SALES'].resample('M').sum()
monthly_sales_dollars_kiwano = df_kiwano['DOLLAR_SALES'].resample('M').sum()

# Calculating Month-over-Month difference in dollars
mom_difference_dollars_avocado = monthly_sales_dollars_avocado.diff()
mom_difference_dollars_kiwano = monthly_sales_dollars_kiwano.diff()

# Plotting the Month-over-Month dollar difference
plt.figure(figsize=(14, 7))
plt.plot(mom_difference_dollars_avocado.index, mom_difference_dollars_avocado.values, marker='o', linestyle='-', label='Avocado')
plt.plot(mom_difference_dollars_kiwano.index, mom_difference_dollars_kiwano.values, marker='^', linestyle='-', label='Kiwano')

# Setting title, labels, and legend
plt.title('Month-over-Month Dollar Difference in Sales for Avocado and Kiwano  Flavored Items - SWIRE-CC')
plt.xlabel('Month')
plt.ylabel('Dollar Difference ($)')

# Formatting the x-axis to show month names and year
plt.gca().xaxis.set_major_locator(mdates.MonthLocator())
plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%b %Y'))

# Rotating date labels for better readability
plt.xticks(rotation=45)

# Formatting y-axis tick labels to show dollar values
plt.gca().yaxis.set_major_formatter(ticker.StrMethodFormatter('${x:,.0f}'))

# Addding grid and legend
plt.grid(True, which='both', linestyle='--', linewidth=0.5)
plt.legend()

plt.tight_layout()
plt.show()

<ipython-input-20-0459aa881ac7>:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df_avocado['DATE'] = pd.to_datetime(df_avocado['DATE'])
<ipython-input-20-0459aa881ac7>:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df_kiwano['DATE'] = pd.to_datetime(df_kiwano['DATE'])

	DATE	MARKET_KEY	CALORIC_SEGMENT	CATEGORY	UNIT_SALES	DOLLAR_SALES	MANUFACTURER	BRAND	PACKAGE	ITEM
0	2021-08-21	524	DIET/LIGHT	SSD	69.0	389.74	SWIRE-CC	DIET YAWN	12SMALL 12ONE CUP	YAWN ZERO SUGAR GENTLE DRINK SUPER-JUICE DURI...
1	2022-05-07	637	REGULAR	SSD	4.0	30.96	COCOS	GORGEOUS ORANGEOUS	12SMALL 12ONE CUP	GORGEOUS SUNSET OUS GENTLE DRINK AVOCADO CUP ...
2	2022-10-22	628	DIET/LIGHT	ING ENHANCED WATER	1.0	2.25	JOLLYS	DIGRESS FLAVORED	20SMALL MULTI JUG	DIGRESS ZERO NUTRIENT ENHANCED WATER BVRG PURP...
3	2022-08-13	216	REGULAR	SSD	3.0	7.55	COCOS	CHERRY FIZZ	1L MULTI JUG	KOOL! RED GENTLE DRINK RED COLA CONTOUR JUG ...
4	2022-01-01	210	REGULAR	SSD	4.0	25.96	COCOS	RADIANT'S	12SMALL 12ONE CUP	RADIANT'S GENTLE DRINK GINGER ALE CUP 12 LIQUI...

	Geography Name	Zip	City	State	Segment	Criteria	Count	Criteria_Unit
0	85281 Tempe, AZ	85281	Tempe	AZ	Household Income	Income Under $10,000	3628	HHs
1	85705 Tucson, AZ	85705	Tucson	AZ	Household Income	Income Under $10,000	3299	HHs
2	85364 Yuma, AZ	85364	Yuma	AZ	Household Income	Income Under $10,000	2852	HHs
3	85301 Glendale, AZ	85301	Glendale	AZ	Household Income	Income Under $10,000	2425	HHs
4	85719 Tucson, AZ	85719	Tucson	AZ	Household Income	Income Under $10,000	2305	HHs

	ZIP_CODE	MARKET_KEY
0	57714	161
1	57772	161
2	67701	1135
3	67740	1135
4	67748	1135

	ITEM	DATE	UNIT_SALES	WOW_CHANGE
102929	ELF BUBBLES GENTLE DRINK SUPER-JUICE DURIAN ...	2020-12-13	343898.0	4.131050
102930	ELF BUBBLES GENTLE DRINK SUPER-JUICE DURIAN ...	2020-12-20	345280.0	0.401863
102931	ELF BUBBLES GENTLE DRINK SUPER-JUICE DURIAN ...	2020-12-27	366213.0	6.062616
102932	ELF BUBBLES GENTLE DRINK SUPER-JUICE DURIAN ...	2021-01-03	338394.0	-7.596399
102933	ELF BUBBLES GENTLE DRINK SUPER-JUICE DURIAN ...	2021-01-10	333110.0	-1.561493
102934	ELF BUBBLES GENTLE DRINK SUPER-JUICE DURIAN ...	2021-01-17	328411.0	-1.410645
102935	ELF BUBBLES GENTLE DRINK SUPER-JUICE DURIAN ...	2021-01-24	308299.0	-6.124034
102936	ELF BUBBLES GENTLE DRINK SUPER-JUICE DURIAN ...	2021-01-31	300295.0	-2.596181
102937	ELF BUBBLES GENTLE DRINK SUPER-JUICE DURIAN ...	2021-02-07	327062.0	8.913568

	PACKAGE	DATE	UNIT_SALES	DOLLAR_SALES
0	.5L 12ONE JUG	2020-12-05	31096.0	189493.73
1	.5L 12ONE JUG	2020-12-12	34702.0	208506.87
2	.5L 12ONE JUG	2020-12-19	36665.0	217931.78
3	.5L 12ONE JUG	2020-12-26	37157.0	219544.90
4	.5L 12ONE JUG	2021-01-02	41999.0	248498.32
5	.5L 12ONE JUG	2021-01-09	41752.0	250142.88
6	.5L 12ONE JUG	2021-01-16	41503.0	250047.56
7	.5L 12ONE JUG	2021-01-23	46458.0	279251.01
8	.5L 12ONE JUG	2021-01-30	46610.0	281192.44
9	.5L 12ONE JUG	2021-02-06	48973.0	295986.68

Capstone Completion EDA - Swire Coca Cola Forecasting¶

Table Of Contents¶

Introduction - Business Problem Statement ¶

Importing Libraries ¶

Import Dataset ¶

Understanding the data ¶

General Exploration of the dataset ¶

Q1. What is the trend in demand for top 10 products over time? ¶

Q2. What is the relation between UNIT SALES and DOLLAR SALES? ¶

Q3. What is the week-over-week percentage change in unit sales for the products? ¶

Manufacture Data Analysis ¶

Understanding the Manufacture data ¶

Q1. What is the market size of different manufacturing categories? ¶

Q2. What is the 'Swire-cc' market size? ¶

Q3. What is the market size of Caloric Segements 'DIET/LIGHT' and 'REGULAR' in 'SWIRE-CC'? ¶

Q4. What is the market size of different categories in 'Swire-cc' and diet caloric segment? ¶

Q5. What is the market size of 'SSD' and 'ING ENHANCED WATER' categories in 'SWIRE-CC' manufacturer and 'REGULAR' caloric segment? ¶

Category Data Analysis ¶

Q1. What are the Unit/Dollar Sales for Category over time? ¶

Q2. How can we understand the diversity of Category by Brand? ¶

Q3. What is the overall demand in Diet Category over time? ¶

Q4. What is the overall demand in Diet Category for 'Swire' manufacturer over time with calorie and also brand analysis? ¶

Caloric Segment Analysis ¶

Q1. What is the distribution of dollar sales and unit sales of Caloric Segment per Year? ¶

Q2. What is the distribution of Caloric Segment over Region Demographics? ¶

Package Type Data Analysis ¶

Q1. What is the distribution of Package Type over Category? ¶

Q2. What are the Unit Sales over Time? ¶

Flavor Analysis ¶

Q1. How to know the frequency of each word in different flavored type? ¶

Q2. Calculate month over month sales of each flavored type? ¶

Q3. What is the distribution of flavor types with different age groups? ¶

Results ¶

Team Contribution ¶

Conclusion ¶